package yxy.apple.db;

import com.alibaba.druid.pool.DruidDataSource;

import java.sql.*;
import java.util.Date;
import java.util.*;

/**
 * Author HYB
 */
public class DbManager {

    static DruidDataSource ds;

    private static DbManager instance = new DbManager();
    private final static int TYPE_INT = 0;
    private final static int TYPE_DATE = 1;
    private final static int TYPE_STRING = 2;
    private final static int TYPE_BOOL = 3;
    private final static int TYPE_LONG = 4;

    public static DbManager instance() {
        return instance;
    }

    public void init(DbNodeConfig config) {
        try {
            ds = new DruidDataSource();
            try {
                ds.setDriverClassName(config.driver);
            } catch (Exception ex) {
                String errorMsg = "Invalid jdbc driver class : " + config.driver;
                //log.error(errorMsg, ex);
                throw new DbException(errorMsg);
            }

            String connStr = String.format("jdbc:mysql://%s/%s?autoReconnect=true", config.ip,
                    config.dbName);
            connStr += "&useServerPreparedStmts=false&rewriteBatchedStatements=true&autoReconnect=true&useSSL=false";
            ds.setUsername(config.user);
            ds.setPassword(config.password);
            ds.setUrl(connStr);
            ds.setInitialSize(config.poolMin);
            ds.setMinIdle(10);
            ds.setMaxActive(config.poolMax);
            //用一些github推荐配置
            ds.setMaxWait(6000);
            ds.setTimeBetweenEvictionRunsMillis(60000);
            ds.setMinEvictableIdleTimeMillis(300000);
            ds.setValidationQuery("SELECT 1");
            ds.setTestWhileIdle(true);
            ds.setTestOnBorrow(false);
            ds.setTestOnReturn(false);
            //打开PSCache，并且指定每个连接上PSCache的大小
            ds.setPoolPreparedStatements(true);
            ds.setMaxPoolPreparedStatementPerConnectionSize(20);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public DbManager() {
    }

    private Connection getConnection() {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            throw new DbException("Database connection pool get connection error:", e);
        }
    }

    public static Connection getConnectionNew() throws SQLException {
        return ds.getConnection();
    }

    public <T> T executeScalarObject(String cmd, ResultObjectBuilder<T> builder) {
        return executeScalarObject(cmd, null, builder);
    }

    public <T> T executeScalarObject(String cmd, Object[] params, ResultObjectBuilder<T> builder) {
        ArrayList<T> list = executeQueryObjectList(cmd, params, builder);
        return list != null && list.size() > 0 ? list.get(0) : null;
    }

    public <T> ArrayList<T> executeQueryObjectList(String cmd, ResultObjectBuilder<T> builder) {
        return executeQueryObjectList(cmd, null, builder);
    }

    public <T> ArrayList<T> executeQueryObjectList(String cmd, Object[] params, ResultObjectBuilder<T> builder) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ArrayList<T> objectList = null;
        try {
            conn = getConnection();
            stmt = this.prepareStatement(conn, cmd, params);
            rs = stmt.executeQuery();
            objectList = getObjectListFromResultSet(rs, builder);
        } catch (Exception e) {
            e.printStackTrace();
            //log.error("executeQueryObjectList", cmd, params, e);
        } finally {
            releaseDbResource(rs, stmt, conn);
        }
        return objectList;
    }


    public Integer executeScalarInt(String cmd) {
        return this.executeScalarInt(cmd, null);
    }

    public Integer executeScalarInt(String cmd, Object[] params) {
        return (Integer) this.executeScalar(cmd, params, TYPE_INT);
    }

    public String executeScalarString(String cmd) {
        return executeScalarString(cmd, null);
    }

    public String executeScalarString(String cmd, Object[] params) {
        return (String) this.executeScalar(cmd, params, TYPE_STRING);
    }

    public Date executeScalarDate(String cmd) {
        return executeScalarDate(cmd, null);
    }

    public Date executeScalarDate(String cmd, Object[] params) {
        return (Date) this.executeScalar(cmd, params, TYPE_DATE);
    }

    public Boolean executeScalarBool(String cmd) {
        return executeScalarBool(cmd, null);
    }

    public Boolean executeScalarBool(String cmd, Object[] params) {
        return (Boolean) this.executeScalar(cmd, params, TYPE_BOOL);
    }

    public Long executeScalarLong(String cmd) {
        return executeScalarLong(cmd, null);
    }

    public Long executeScalarLong(String cmd, Object[] params) {
        return (Long) this.executeScalar(cmd, params, TYPE_LONG);
    }

    private Object executeScalar(String cmd, Object[] params, int type) {
        Object result = null;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stmt = this.prepareStatement(conn, cmd, params);
            rs = stmt.executeQuery();
            if (rs.next()) {
                switch (type) {
                    case TYPE_INT:
                        int intVal = rs.getInt(1);
                        result = rs.wasNull() ? null : intVal;
                        break;
                    case TYPE_LONG:
                        long longVal = rs.getLong(1);
                        result = rs.wasNull() ? null : longVal;
                        break;
                    case TYPE_STRING:
                        result = rs.getString(1);
                        break;
                    case TYPE_DATE:
                        Timestamp tsVal = rs.getTimestamp(1);
                        result = rs.wasNull() ? null : new Date(tsVal.getTime());
                        break;
                    case TYPE_BOOL:
                        boolean blVal = rs.getBoolean(1);
                        result = rs.wasNull() ? null : blVal;
                        break;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            //log.error("executeScalar", cmd, params, e);
        } finally {
            releaseDbResource(rs, stmt, conn);
        }
        return result;
    }

    static <T> ArrayList<T> getObjectListFromResultSet(ResultSet rs, ResultObjectBuilder<T> builder) {
        if (rs != null) {
            ArrayList<T> objectList = null;
            try {
                objectList = new ArrayList<>();
                while (rs.next()) {
                    objectList.add(builder.build(rs));
                }
            } catch (SQLException e) {
                e.printStackTrace();
                //log.error("getObjectListFromResultSet", e);
            }
            return objectList;
        } else {
            return null;
        }
    }

    public int executeCommand(String cmd) {
        return executeCommand(cmd, null);
    }

    //删除,插入,更新
    public int executeCommand(String cmd, Object[] params) {
        Connection conn = null;
        PreparedStatement stmt = null;
        int result = 0;
        try {
            conn = getConnection();
            beginTransaction(conn);
            stmt = this.prepareStatement(conn, cmd, params);
            result = stmt.executeUpdate();
            commitTransaction(conn);
        } catch (Exception e) {
            e.printStackTrace();
            //log.error("executeCommand", cmd, params, e);
            rollBackTransaction(conn);
        } finally {
            releaseDbResource(stmt, conn);
        }
        return result;
    }

    //删除,插入,更新
    public int executeCommand(Connection conn, String cmd, Object[] params) throws SQLException {
        PreparedStatement stmt = this.prepareStatement(conn, cmd, params);
        int result = stmt.executeUpdate();
        releaseDbResource(stmt, null);
        return result;
    }

    private PreparedStatement prepareStatement(Connection conn, String cmd, Object[] params) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement(cmd);
        if (params != null) {
            setParams(stmt, params);
        }
        return stmt;
    }

    // 为PreparedStatement设置参数
    private void setParams(PreparedStatement stmt, Object[] params) throws SQLException {
        Object o;
        for (int i = 0; i < params.length; i++) {
            o = params[i];
            if (o instanceof Integer) {
                stmt.setInt(i + 1, (Integer) o);
            } else if (o instanceof Short) {
                stmt.setShort(i + 1, (Short) o);
            } else if (o instanceof Long) {
                stmt.setLong(i + 1, (Long) o);
            } else if (o instanceof String) {
                stmt.setString(i + 1, (String) o);
            } else if (o instanceof Date) {
                stmt.setObject(i + 1, o);
            } else if (o instanceof Boolean) {
                stmt.setBoolean(i + 1, (Boolean) o);
            } else if (o instanceof byte[]) {
                stmt.setBytes(i + 1, (byte[]) o);
            } else if (o instanceof Double) {
                stmt.setDouble(i + 1, (Double) o);
            } else if (o instanceof Float) {
                stmt.setFloat(i + 1, (Float) o);
            } else if (o == null) {
                stmt.setNull(i + 1, Types.OTHER);
            } else {
                throw new SQLException("Not allowed dataBase data type");
            }
        }
    }

    public static void beginTransaction(Connection conn) throws SQLException {
        if (conn != null && conn.getAutoCommit()) {
            conn.setAutoCommit(false);
        }
    }

    public static void commitTransaction(Connection conn) throws SQLException {
        if (conn != null && !conn.getAutoCommit()) {
            conn.commit();
        }
    }

    public static void rollBackTransaction(Connection conn) {
        try {
            if (conn != null && !conn.getAutoCommit()) {
                conn.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            //log.error("rollBackTransaction", e);
        }
    }

    public static void releaseDbResource(Connection conn) {
        releaseDbResource(null, null, conn);
    }

    private static void releaseDbResource(Statement stmt, Connection conn) {
        releaseDbResource(null, stmt, conn);
    }

    private static void releaseDbResource(ResultSet rs, Statement stmt, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
            //log.error(ex.getMessage(), ex);
        }
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException ex) {
            //log.error(ex.getMessage(), ex);
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex) {
            //log.error(ex.getMessage(), ex);
        }
    }

    public <T> int[] executeBatchCommand(String cmd, Collection<T> valuesList, IParamsBuilder<T> builder) {
        Collection<Object[]> paramsList = new ArrayList<>(valuesList.size());
        for (T t : valuesList) {
            Object[] params = builder.buildParams(t);
            paramsList.add(params);
        }
        return executeBatchCommand(cmd, paramsList);
    }

    public int[] executeBatchCommand(String cmd, Collection<Object[]> paramsList) {
        Connection conn = null;
        PreparedStatement stmt = null;
        int[] rowCounts = null;
        try {
            conn = getConnection();
            beginTransaction(conn);
            stmt = this.prepareBatchStatement(conn, cmd, paramsList);
            rowCounts = stmt.executeBatch();
            commitTransaction(conn);
        } catch (Exception e) {
            try {
                rollBackTransaction(conn);
            } catch (Exception e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
            //logBatchCmdError("executeBatchCommand", cmd, paramsList, e, throwEx);
        } finally {
            releaseDbResource(null, stmt, conn);
        }
        return rowCounts;
    }

    public PreparedStatement prepareBatchStatement(Connection conn, String cmd, Collection<Object[]> paramsList)
            throws SQLException {
        PreparedStatement stmt = conn.prepareStatement(cmd);
        if (paramsList != null && paramsList.size() != 0) {
            for (Object[] params : paramsList) {
                setParams(stmt, params);
                stmt.addBatch();
            }
        }
        return stmt;
    }

}
